package com.sxyjhh.dbutil.basic;

import com.sxyjhh.dbutil.utils.PageInfo;
import lombok.extern.log4j.Log4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.AbstractListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * Description:分页函数
 *
 * @JDK Version:1.8.0_40
 * Version: V1.0
 * Author: wangchun
 * Date: 2016/3/30 10:05
 */

public class Pagination {
    private static Log logger = LogFactory.getLog(Pagination.class);

    private ConstConfig.DBType dbType; //:mysql;:oracle:mssql;
    /**
     * 一页显示的记录数
     */
    private int numPerPage = 0;
    /**
     * 记录总数
     */
    private long totalRows = 0;
    /**
     * 总页数
     */
    private long totalPages = 0;
    /**
     * 当前页码
     */
    private long currentPage = 1;
    /**
     * 起始行数
     */
    private long startIndex = 0;
    /**
     * 结束行数
     */
    private long lastIndex = 0;
    /**
     * 指定类型结果列表
     */
    private List<Map> resultList = null;
    /**
     * 查询sql语句
     */
    private String querySql = null;
    /**
     * 计数sql语句
     */
    private String countSql = null;
    /**
     * 是否优化 (sql中有多个from或多个order by时置为false)
     */
    private boolean optimizeCountSql = true;
    /**
     * SQL 绑定参数
     */
    private Object[] params = null;
    /**
     * 数据结果Handler
     */
    private AbstractListHandler handler;
    /**
     * 查询器
     */
    private QueryRunner queryRunner;

    private String tableName;

    public Pagination(ConstConfig.DBType dbType, String sql, long currentPage, int numPerPage, AbstractListHandler handler, QueryRunner queryRunner, String tableName) {
        this.dbType = dbType;
        this.querySql = sql;
        this.currentPage = currentPage;
        this.numPerPage = numPerPage;
        this.handler = handler;
        this.queryRunner = queryRunner;
        this.tableName = tableName;
    }

    public Pagination(ConstConfig.DBType dbType, String sql, long currentPage, int numPerPage, AbstractListHandler handler, QueryRunner queryRunner, String tableName, long totalRows) {
        this.dbType = dbType;
        this.querySql = sql;
        this.currentPage = currentPage;
        this.numPerPage = numPerPage;
        this.handler = handler;
        this.queryRunner = queryRunner;
        this.tableName = tableName;
        this.totalRows = totalRows;
    }

    public Pagination(ConstConfig.DBType dbType, String sql, long currentPage, int numPerPage, AbstractListHandler handler, QueryRunner queryRunner, Object[] params) {
        this.dbType = dbType;
        this.querySql = sql;
        this.currentPage = currentPage;
        this.numPerPage = numPerPage;
        this.handler = handler;
        this.queryRunner = queryRunner;
        this.params = params;
    }

    public String getCountSql(String querySql) {
        StringBuffer sb = new StringBuffer("select count(1) from (");
        sb.append(querySql);
        sb.append(") z");
        return sb.toString();
    }

    public String getCountFastSql(String querySql) {
        String sql = querySql;
        StringBuffer sb = new StringBuffer("select count(1) ");
        int orderPos = sql.lastIndexOf("order by");
        int fromPos = sql.indexOf("from");
        if (orderPos > 0)
            sb.append(sql.substring(fromPos, orderPos));
        else
            sb.append(sql.substring(fromPos));
        return sb.toString();
    }

    public PageInfo queryData(String pkColumnName) throws Exception {
        if (this.queryRunner == null) {
            throw new IllegalArgumentException(
                    "com.sxyjhh.daqc_dbutils.basic.Pagination.queryData is null,please initial it first. ");
        } else if (querySql == null || querySql.equals("")) {
            throw new IllegalArgumentException(
                    "com.sxyjhh.daqc_dbutils.basic.Pagination.queryData is empty,please initial it first. ");
        }

        String totalSQL = getTotalSQL();

        // 总记录数

        Long count = null;
        if (this.totalRows <= 0) {
            Number countObj = null;
            if (params == null) {
                countObj = queryRunner.query(totalSQL, new ScalarHandler<Number>());
            } else {
                countObj = queryRunner.query(totalSQL, params, new ScalarHandler<Number>());
            }
            //oracle 查询count返回的是BigDecimal数据类型，Mysql查询count返回的是BigDecimal数据类型是Long
            if (countObj instanceof BigDecimal) {
                BigDecimal bigDecimalCount = (BigDecimal) countObj;
                count = bigDecimalCount.longValue();
            } else if (countObj instanceof Long) {
                count = (Long) countObj;
            } else if (countObj instanceof Integer) {
                count = countObj.longValue();
            }
            this.totalRows = (count == null ? 0 : count);
        }

        // 计算总页数
        setTotalPages();
        // 计算起始行数
        setStartIndex();
        // 计算结束行数
        setLastIndex();


        StringBuffer paginationSQL = new StringBuffer();
        switch (this.dbType) {
            case MYSQL:
                // 构造mysql数据库的分页语句
                paginationSQL.append("select * from ( ");
                paginationSQL.append(querySql);
                paginationSQL.append(" limit " + startIndex + "," + numPerPage);
                paginationSQL.append(") z");
                break;
            case ORACLE:
                // 构造oracle数据库的分页语句
                paginationSQL.append("SELECT * FROM ( ");
                paginationSQL.append("SELECT temp.* ,ROWNUM num FROM ( ");
                paginationSQL.append(querySql);
                paginationSQL.append("　) temp where ROWNUM <= " + lastIndex);
                paginationSQL.append(" ) WHERE　num > " + startIndex);
                break;
            case DAMENG:
                // 构造oracle数据库的分页语句
                paginationSQL.append("select * from ( ");
                paginationSQL.append(querySql);
                paginationSQL.append(" limit " + startIndex + "," + numPerPage);
                paginationSQL.append(") z");
                break;
            case SQLSERVER:
                /*
                SELECT TOP (1000)
                    *
                    FROM  ( SELECT TOP ( 1000 * 4 ) * FROM  [TEST].[dbo].[tm_yf_user] ORDER BY  user_register DESC ) AS product
                ORDER BY product.user_register
                */

                /*
                SELECT  *
                        FROM dbo.Products
                ORDER   BY UnitPrice DESC
                OFFSET  ( @pageSize * ( @pageIndex - 1 )) ROWS
                FETCH NEXT @pageSize ROWS ONLY;
                */

                long totalTopCount = numPerPage * ((currentPage - 1) - 1) + numPerPage;

                /*paginationSQL.append("SELECT TOP (");
                paginationSQL.append(numPerPage);
                paginationSQL.append(") ");
                paginationSQL.append(" * ");
                paginationSQL.append(" FROM " + tableName);
                paginationSQL.append(" WHERE " + pkColumnName + " NOT IN ");
                paginationSQL.append(" ( SELECT TOP (" + totalTopCount + ") " + pkColumnName + " FROM  (" + querySql + ") Z ORDER BY  Z." + pkColumnName + " DESC ) ");
                paginationSQL.append(" ORDER BY " + pkColumnName + " DESC ");*/

                paginationSQL.append("SELECT  * ");
                paginationSQL.append(" FROM  (" + querySql + ") Z  ");
                paginationSQL.append(" ORDER BY  Z." + pkColumnName + " DESC ");
                paginationSQL.append(" OFFSET " + numPerPage * (currentPage - 1) + " ROWS ");
                paginationSQL.append(" FETCH NEXT " + numPerPage + " ROWS ONLY ");

                break;
            default:
                break;
        }


        // 装入结果集List
        String paginationQuerySQL = paginationSQL.toString();
        logger.info("paginationQuerySQL==---------" + paginationQuerySQL);
        if (this.params == null) {
            this.resultList = (List<Map>) queryRunner.query(paginationQuerySQL, handler);
        } else {
            this.resultList = (List<Map>) queryRunner.query(paginationQuerySQL, params, handler);
        }

        return new PageInfo(totalPages, currentPage, totalRows, resultList.size(), numPerPage, resultList);
    }

    public PageInfo queryPageinfo() throws Exception {
        if (this.queryRunner == null) {
            throw new IllegalArgumentException(
                    "com.sxyjhh.daqc_dbutils.basic.Pagination.queryPageinfo is null,please initial it first. ");
        } else if (querySql == null || querySql.equals("")) {
            throw new IllegalArgumentException(
                    "com.sxyjhh.daqc_dbutils.basic.Pagination.querySql is empty,please initial it first. ");
        }

        String totalSQL = getTotalSQL();
        // 总记录数

        logger.info("totalSQL=" + totalSQL);
        Number countObj = null;
        if (params == null) {
            countObj = queryRunner.query(totalSQL, new ScalarHandler<Number>());
        } else {
            countObj = queryRunner.query(totalSQL, params, new ScalarHandler<Number>());
        }
        Long count = null;
        //oracle 查询count返回的是BigDecimal数据类型，Mysql查询count返回的是BigDecimal数据类型是Long
        if (countObj instanceof BigDecimal) {
            BigDecimal bigDecimalCount = (BigDecimal) countObj;
            count = bigDecimalCount.longValue();
        } else if (countObj instanceof Long) {
            count = (Long) countObj;
        } else if (countObj instanceof Integer) {
            count = countObj.longValue();
        }
        this.totalRows = (count == null ? 0 : count);

        // 计算总页数
        setTotalPages();
        // 计算起始行数
        setStartIndex();
        // 计算结束行数
        setLastIndex();

        return new PageInfo(totalPages, currentPage, totalRows, 0, numPerPage, null);
    }

    private String getTotalSQL() {
        return countSql == null ? (isOptimizeCountSql() ? getCountFastSql(querySql) : getCountSql(querySql)) : countSql;
    }

    // 计算总页数
    public void setTotalPages() {
        if (totalRows % numPerPage == 0) {
            this.totalPages = totalRows / numPerPage;
        } else {
            this.totalPages = (totalRows / numPerPage) + 1;
        }
        if (this.currentPage > this.totalPages) this.currentPage = this.totalPages;
        if (this.currentPage < 1) this.currentPage = 1;
    }


    public void setStartIndex() {
        this.startIndex = (currentPage - 1) * numPerPage;
    }

    // 计算结束时候的索引
    public void setLastIndex() {
        /*if(currentPage==-1){//如果当前页为-1,则认为需要显示全部
            this.lastIndex = totalRows;
			return;
		}*/
        if (totalRows < numPerPage) {
            this.lastIndex = totalRows;
        } else if ((totalRows % numPerPage == 0)
                || (totalRows % numPerPage != 0 && currentPage < totalPages)) {
            this.lastIndex = currentPage * numPerPage;
        } else if (totalRows % numPerPage != 0 && currentPage == totalPages) {// 最后一页
            this.lastIndex = totalRows;
        }
    }

    public boolean isOptimizeCountSql() {
        return optimizeCountSql;
    }

    public void setOptimizeCountSql(boolean optimizeCountSql) {
        this.optimizeCountSql = optimizeCountSql;
    }

}